﻿using System;
using System.Collections.Generic;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
using WAD.Model;
using System.Reflection;
using System.Configuration;

namespace WAD.DAL
{
    public class DAO
    {
        public static string DBName = "db_wad";
        /// <summary>
        /// 获取Database对象
        /// </summary>
        /// <returns></returns> 
        protected static Database GetDatabase()
        {
            return DatabaseFactory.CreateDatabase();
        }

        /// <summary>
        /// 获取分页列表
        /// </summary>
        /// <param name="fieldList">要查询的字段</param>
        /// <param name="orderBy">排序字段</param>
        /// <param name="pageIndex">当前查询的页码</param>
        /// <param name="pageSize">每页显示的总数</param>
        /// <param name="where">条件</param>
        /// <param name="tableName">表名</param>
        /// <returns></returns>
        protected static PageData GetPageList(String fieldList, String orderBy, int pageIndex, int pageSize, String where, String tableName)
        {
            PageData pageData = new PageData();
            Database database = GetDatabase();
            DbCommand cmd = database.GetStoredProcCommand("GetPageData");
            database.AddInParameter(cmd, "@TableName", DbType.String, tableName);
            database.AddInParameter(cmd, "@FieldList", DbType.String, fieldList);
            database.AddInParameter(cmd, "@OrderBy", DbType.String, orderBy);
            database.AddInParameter(cmd, "@Where", DbType.String, where);
            database.AddInParameter(cmd, "@PageSize", DbType.Int32, pageSize);
            database.AddInParameter(cmd, "@PageIndex", DbType.Int32, pageIndex);
            database.AddOutParameter(cmd, "@ItemCount", DbType.Int32, 4);
            database.AddOutParameter(cmd, "@PageCount", DbType.Int32, 4);

            DataSet ds = database.ExecuteDataSet(cmd);
            int itemCount = Convert.ToInt32(database.GetParameterValue(cmd, "@ItemCount"));
            int pageCount = Convert.ToInt32(database.GetParameterValue(cmd, "@PageCount"));
            pageData.table = ds.Tables[0];
            pageData.pageSize = pageSize;
            pageData.pageIndex = pageIndex;

            //总页数
            pageData.pageCount = pageCount;

            //数据总条数
            pageData.itemCount = itemCount;
            return pageData;
        }

        /// <summary>
        /// 获取列表到list对象
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        protected static List<Dictionary<string, object>> GetList(String sql)
        {
            Database db = GetDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sql);
            List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
            using (IDataReader dr = db.ExecuteReader(cmd))
            {
                while (dr.Read())
                {
                    Dictionary<string, object> map = new Dictionary<string, object>();
                    //遍历列
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        map.Add(dr.GetName(i), dr[i]);
                    }
                    list.Add(map);
                }
            }
            return list;
        }

        /// <summary>
        /// 执行影响行数的sql  如: delete update insert
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(String sql)
        {
            Database database = GetDatabase();
            DbCommand cmd = database.GetSqlStringCommand(sql);
            int row = database.ExecuteNonQuery(cmd);
            return row;
        }

        /// <summary>
        /// 将查询结果绑定到一个实体对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="model"></param>
        /// <returns></returns>
        protected static object BindEntity(String sql, object model)
        {
            Database db = GetDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sql);
            IDataReader dr = null;
            if (model == null) return null;
            try
            {
                dr = db.ExecuteReader(cmd);
                if (dr.Read())
                {
                    //遍历列
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        var value = dr[i];

                        if (value.GetType() == System.DBNull.Value.GetType())
                        {
                            continue; //为空
                        }

                        //获取列名
                        String col = dr.GetName(i);

                        //获取属性
                        PropertyInfo property = model.GetType().GetProperty(col);
                        if (property != null)
                            property.SetValue(model, value, null);
                    }
                }
                else
                {
                    model = null;
                }
            }
            catch (Exception e)
            {
                model = null;
            }
            finally
            {
                if (dr != null)
                {
                    dr.Close();
                }
            }
            return model;
        }

        /// <summary>
        /// 将查询结果绑定到一个Map对象
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        protected static Dictionary<string, object> BindMap(String sql)
        {
            Database db = GetDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sql);
            IDataReader dr = null;
            Dictionary<string, object> map = null;
            try
            {
                dr = db.ExecuteReader(cmd);
                if (dr.Read())
                {
                    map = new Dictionary<string, object>();
                    //遍历列
                    for (int i = 0; i < dr.FieldCount; i++)
                    {
                        var value = dr[i];
                        //获取列名
                        String col = dr.GetName(i);
                        map.Add(col, value);
                    }
                }
            }
            catch { map = null; }
            finally
            {
                if (dr != null)
                    dr.Close();
            }
            return map;
        }
        /// <summary>
        /// 返回第一行的第一条数据
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql)
        {
            Database database = GetDatabase();
            DbCommand cmd = database.GetSqlStringCommand(sql);
            return database.ExecuteScalar(cmd);
        }

        public static Dictionary<string, object> Get(string tableName, string where, string fileds = "")
        {
            if (string.IsNullOrEmpty(fileds))
                fileds = "*";
            if (!string.IsNullOrEmpty(where))
                where = " where " + where;
            return BindMap(string.Format("select {0} from {1}.dbo.[{2}] {3} ", fileds, DBName, tableName, where));
        }

        public static Dictionary<string, object> GetLeftJoin(string leftTable, string rightTable, string onWhere, string where, string fileds = "")
        {
            if (string.IsNullOrEmpty(fileds))
                fileds = "*";
            if (!string.IsNullOrEmpty(where))
                where = " where " + where;
            return BindMap(string.Format("select top 1 {0} from {1}.dbo.[{2}] left join {1}.dbo.[{3}] on {4} {5}", fileds, DBName, leftTable, rightTable, onWhere, where));
        }

        public static PageData GetListPage(String fieldList, String orderBy, int pageIndex, int pageSize, String where, String tableName)
        {
            return GetPageList(fieldList, orderBy, pageIndex, pageSize, where, tableName);
        }

        public static List<Dictionary<string, object>> GetList(string where, int top, string tableName, string fields = "*")
        {
            return GetList(string.Format("select top {0} {1} from {2} where {3}", top, fields, tableName, where));
        }
        
        public static PageData GetList(int top, string where, string tableName, string fileds = "*", string orderby = "")
        {
            PageData pageData = new PageData();
            Database db = GetDatabase();
            DbCommand cmd = null;
            if (string.IsNullOrEmpty(orderby))
                cmd = db.GetSqlStringCommand(string.Format("select top {0} {1} from {2} where {3}", top, fileds, tableName, where));
            else
                cmd = db.GetSqlStringCommand(string.Format("select top {0} {1} from {2} where {3} order by {4}", top, fileds, tableName, where, orderby));

            DataSet ds = db.ExecuteDataSet(cmd);
            pageData.table = ds.Tables[0];
            pageData.pageSize = top;
            pageData.pageIndex = 1;
            //总页数
            pageData.pageCount = 1;
            //数据总条数
            pageData.itemCount = pageData.table.Rows.Count;
            return pageData;
        }

        public static PageData GetList(int top, string where, string tableName, string fileds = "*", string orderby = "", string groupby = "")
        {
            System.Text.StringBuilder sb = new System.Text.StringBuilder();
            sb.AppendFormat("select top {0} {1} from {2} where {3}", top, fileds, tableName, where);

            if (!string.IsNullOrEmpty(groupby))
                sb.AppendFormat(" group by {0}", groupby);

            if (!string.IsNullOrEmpty(orderby))
                sb.AppendFormat(" order by {0}", orderby);

            PageData pageData = new PageData();
            Database db = GetDatabase();
            DbCommand cmd = db.GetSqlStringCommand(sb.ToString());
            DataSet ds = db.ExecuteDataSet(cmd);
            pageData.table = ds.Tables[0];
            pageData.pageSize = top;
            pageData.pageIndex = 1;
            //总页数
            pageData.pageCount = 1;
            //数据总条数
            pageData.itemCount = pageData.table.Rows.Count;
            sb.Clear();
            return pageData;
        }

        /// <summary>
        /// 统计行数
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public static int GetCount(string tableName, string where)
        {
            object o = ExecuteScalar(string.Format("select count(1) from {0} where {1}", tableName, where));
            if (o != DBNull.Value)
                return Convert.ToInt32(o);
            return 0;
        }
    }
}
